# Очистка, создание таблиц, наполнение их данными (три автора, книги, студента)
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from app.config import settings

from sqlalchemy import select, text
from datetime import datetime
from models import Base, Authors, Books, Students


# создаем движок, на котором будет работать моя база данных
engine = create_engine(
    url=settings.database_url_psycopg,
    echo=True,
    pool_size=5,
    max_overflow=10,
)

# создадим сессию, которая будет управлять базой данных
session_factory = sessionmaker(engine)


class BookLibrary:
    @staticmethod
    def drop_and_create_tables():
        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)

    @staticmethod
    def insert_authors():
        with session_factory() as session:
            author_1 = Authors(first_name="Лев", last_name="Толстой")
            author_2 = Authors(first_name="Михаил", last_name="Булгаков")
            author_3 = Authors(first_name="Иван", last_name="Бунин")
            session.add_all([author_1, author_2, author_3])
            session.commit()

    @staticmethod
    def insert_books():
        with session_factory() as session:
            book_1 = Books(
                title="Война и мир", author_id=1,
                release_date=datetime.strptime('1867-12-01', '%Y-%m-%d'),
                count=5,
            )
            book_2 = Books(
                title="Собачье сердце", author_id=2,
                release_date=datetime.strptime('1925-01-01', '%Y-%m-%d'),
            )
            book_3 = Books(
                title="Тёмные аллеи", author_id=3,
                release_date=datetime.strptime('1938-02-17', '%Y-%m-%d'),
                count=2,
            )
            session.add_all([book_1, book_2, book_3])
            session.commit()

    @staticmethod
    def insert_students():
        with session_factory() as session:
            # оказывается тип int - есть ограничения по максимальному числу, максимум - 2147483647
            student_1 = Students(first_name="Иван", last_name="Иванов", phone=2147483647, email="ivan@gmail.com")
            student_2 = Students(first_name="Пётр", last_name="Петров", phone=558, email="pyotr@gmail.com")
            student_3 = Students(first_name="John", last_name="Doe", phone=557, email="john_doe@gmail.com")
            session.add_all([student_1, student_2, student_3])
            session.commit()
    #
    # @staticmethod
    # def insert_receiving_books():
    #     book = Students(first_name="Test", last_name="Pythonoff", phone=123, email="test@mail.ru")
    #     book.keywords("")
    #     # print('---------------', book.keywords)
    #     with session_factory() as session:
    #         session.add(book)
    #         session.commit()

    @staticmethod
    def select_from_database():
        with session_factory() as session:
            result = session.execute(select(Authors))
            authors = result.all()
            result = session.execute(select(Books))
            books = result.all()
            result = session.execute(select(Students))
            students = result.all()
            print('Авторы книг:')
            for i in authors:
                print(f"Имя: {i[0].first_name}, Фамилия: {i[0].last_name}")
            print('\nКниги:')
            for i in books:
                print(f"Название: {i[0].title}. Количество: {i[0].count}")
            print('\nСтуденты:')
            for i in students:
                print(f"Имя: {i[0].first_name}, Фамилия: {i[0].last_name}")


BookLibrary.drop_and_create_tables()
BookLibrary.insert_authors()
BookLibrary.insert_books()
BookLibrary.insert_students()
# BookLibrary.insert_receiving_books()
BookLibrary.select_from_database()
